package com.test.contract;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;

import androidx.annotation.Nullable;

import java.util.ArrayList;
import java.util.List;

/**
 * Anchor : Create by CimZzz
 * Time : 2020/06/07 14:26:21
 * Project : contract
 * Since Version : Alpha
 */
public class SqlHelper extends SQLiteOpenHelper {
    public SqlHelper(@Nullable Context context) {
        super(context, "db_contract", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("create table tb_contract (");
        stringBuilder.append("id integer primary key AUTOINCREMENT,");
        stringBuilder.append("name text,");
        stringBuilder.append("work text,");
        stringBuilder.append("phone text,");
        stringBuilder.append("qq text,");
        stringBuilder.append("address text");
        stringBuilder.append(")");
        sqLiteDatabase.execSQL(stringBuilder.toString());
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    public void insert(User user) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into tb_contract ('name', 'work', 'phone', 'qq', 'address') values (?, ?, ?, ?, ?)", new Object[] {
            user.getName(),
            user.getWork(),
            user.getPhone(),
            user.getQq(),
            user.getAddress()
        });
        Cursor cursor = db.rawQuery("select last_insert_rowid() from tb_contract",null);
        cursor.moveToFirst();
        user.setId(cursor.getInt(0));
        cursor.close();
        db.close();
    }

    public void update(User user) {
        SQLiteDatabase db = getWritableDatabase();

        db.execSQL("replace into tb_contract ('id', 'name', 'work', 'phone', 'qq', 'address') values (?, ?, ?, ?, ?, ?)", new Object[] {
            user.getId(),
            user.getName(),
            user.getWork(),
            user.getPhone(),
            user.getQq(),
            user.getAddress()
        });
        db.close();
    }

    public List<User> getContract(String name) {
        ArrayList<User> users = new ArrayList<>();
        SQLiteDatabase db = getWritableDatabase();
        String sql = "select * from tb_contract";
        if(!TextUtils.isEmpty(name)) {
            sql += " where name like '%" + name + "%'";
        }
        Cursor cursor = db.rawQuery(sql, null);
        while(cursor.moveToNext()) {
            User user = new User();
            user.setId(cursor.getInt(cursor.getColumnIndex("id")));
            user.setName(cursor.getString(cursor.getColumnIndex("name")));
            user.setWork(cursor.getString(cursor.getColumnIndex("work")));
            user.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
            user.setQq(cursor.getString(cursor.getColumnIndex("qq")));
            user.setAddress(cursor.getString(cursor.getColumnIndex("address")));
            users.add(user);
        }
        cursor.close();
        db.close();
        return users;
    }


    public void delUser(User user) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("delete from tb_contract where id = ?", new Object[] {
            user.getId()
        });
        db.close();
    }

}
